package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.School;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

@Slf4j public final class SchoolDao {
    private static SchoolDao schoolDao = new SchoolDao();
    //预编译语句盒子
    PreparedStatement preparedStatement = null;
    //结果集
    ResultSet resultSet = null;

    private SchoolDao() {
    }
    //获得SchoolDao实例
    public static SchoolDao getInstance() {
        return schoolDao;
    }

    /**
     * 根据分页请求查询元组
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<School> findAll(Connection connection,Collection<Condition> condition,Pagination pagination) throws SQLException {
        //schools集合用于储存查找到的数据
        Collection<School> schools = new TreeSet<School>();
        //获得元组总数
        int totalNum = CommonDao.getInstance().count(connection,"School");
        //构建查找sql语句
        String sql_select_all = "SELECT * FROM school ";
        if (condition != null){
            sql_select_all += Condition.toWhereClause(JSON.toJSONString(condition));
            log.trace(sql_select_all);
        }
        if (pagination != null){
            //如果带有分页请求，则根据请求创建对应的LIMIT子句
            sql_select_all += pagination.toLimitClause(totalNum);
            log.trace(sql_select_all);
        }
        //预编sql语句
        this.preparedStatement = connection.prepareStatement(sql_select_all);
        //执行，获得结果集
        this.resultSet = preparedStatement.executeQuery();
        //读取数据
        while (this.resultSet.next()){
            //新建foundSchool对象，用于储存查找到的每一行的信息
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no =  resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            School foundSchool = new School(id,description,no,remarks);
            //添加进集合中
            schools.add(foundSchool);
        }
        //关闭结果集、语句盒子、数据库连接
        JdbcHelper.close(this.resultSet,this.preparedStatement);
        //返回查找到的数据的集合
        return schools;
    }

    //根据id查找
    public School find(Connection connection,Integer id) throws SQLException {
        //新建foundSchool对象，用于储存查找的信息
        School foundSchool = new School();
        this.preparedStatement = connection.prepareStatement("select * from School where id=" + id);
        //执行sql语句并获得结果集
        this.resultSet = this.preparedStatement.executeQuery();
        //读取信息并将其赋值给foundSchool
        this.resultSet.next();
        foundSchool.setId(this.resultSet.getInt("id"));
        foundSchool.setDescription(this.resultSet.getString("description"));
        foundSchool.setNo(this.resultSet.getString("no"));
        foundSchool.setRemarks(this.resultSet.getString("remarks"));
        //关闭结果集、语句盒子、数据库连接对象
        JdbcHelper.close(resultSet,preparedStatement);
        //返回带有查找到的数据的对象
        return foundSchool;
    }

    //更新数据库
    public boolean update(Connection connection,School school) throws SQLException {
        //新建预编译语句盒子，并编译sql语句
        this.preparedStatement = connection.prepareStatement("update School set description=?,no=?,remarks=? where id=?");
        //为sql语句参数赋值
        this.preparedStatement.setString(1,school.getDescription());
        this.preparedStatement.setString(2,school.getNo());
        this.preparedStatement.setString(3,school.getRemarks());
        this.preparedStatement.setInt(4,school.getId());
        //执行sql语句，并获得首映的行数的值
        int rowAffected = this.preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        //返回更新判定
        return rowAffected > 0;
    }

    //添加
    public boolean add(Connection connection, School school) throws SQLException {
        this.preparedStatement = connection.prepareStatement("INSERT INTO School (description,no,remarks) VALUES (?,?,?)");
        preparedStatement.setString(1,school.getDescription());
        preparedStatement.setString(2,school.getNo());
        preparedStatement.setString(3,school.getRemarks());
        int rowAffected = this.preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        //返回添加判定
        return rowAffected>0;
    }

    //删除
    public boolean delete(Connection connection,Integer id) throws SQLException {
        this.preparedStatement = connection.prepareStatement("delete from School where id=" + id);
        //执行sql语句并获得受影响的行数
        int rowAffected = preparedStatement.executeUpdate();
        //关闭语句盒子、数据库连接
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }
}
